﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Common;
using Instrument;


namespace UI
{
    class DataBaseOperation
    {
        private static DataBaseOperation _dataBaseOperation;
        private SqlDatabaseProxy _sqlDatabaseProxy;
        public static DataBaseOperation GetInstance()
        {
            return _dataBaseOperation ?? (_dataBaseOperation = new DataBaseOperation());
        }
        private DataBaseOperation()
        {
            _sqlDatabaseProxy = new SqlDatabaseProxy();
        }
        /// <summary>
        /// 创建产品测试表
        /// </summary>
        /// <param name="production"></param>
        /// <returns></returns>
        public bool CreatMeasureTable(Production production)
        {
            try
            {
                string tableName = production.Model + "_MeasureRecord";
                string ColumnsDefine = "";
                for (int i = 1; i <= production.ResistanceCount; i++)
                {
                    ColumnsDefine += "电阻" + i.ToString() + " Float,";
                }
                string sqlString = "CREATE TABLE " + tableName + "(MeasureNum int IDENTITY NOT NULL PRIMARY KEY,MeasureTime SmallDateTime,"
                     + ColumnsDefine + "Quality varchar(20),Operator varchar(10));";
                _sqlDatabaseProxy.Execute(sqlString);
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 测试结果录入数据库
        /// </summary>
        /// <param name="production"></param>
        /// <param name="employee"></param>
        public void InsertResult(Production production, Employee employee)
        {
            string tableName = production.Model + "_MeasureRecord";
            StringBuilder resistanceValuesStringbiulder = new StringBuilder();
            foreach (var item in production.ResistanceValue)
            {
                resistanceValuesStringbiulder.Append("," + item.ToString("F2"));
            }
            resistanceValuesStringbiulder.Remove(0, 1);
            string resistanceValueString = resistanceValuesStringbiulder.ToString();
            string ColumnsName = "";
            for (int i = 1; i <= production.ResistanceCount; i++)
            {
                ColumnsName += "电阻" + i.ToString() + ",";
            }
            string sqlString = "INSERT INTO " + tableName + "(MeasureTime," + ColumnsName + "Quality,Operator)"
                + " VALUES('" + DateTime.Now.ToString("yyyy-MM-dd hh:mm") + "',"
                + resistanceValueString + ",'" + production.ProductionQuality + "','" + employee.JobNum + "');";
            _sqlDatabaseProxy.Execute(sqlString);
        }
        /// <summary>
        /// 查找匹配的用户，若存在则返回该用户，若不存在返回null
        /// </summary>
        /// <param name="JobNum"></param>
        /// <param name="PassWord"></param>
        /// <returns></returns>
        public Employee MatchUserID(string JobNum, string PassWord)
        {
            Employee employee = null;
            string sqlString = "SELECT * FROM UserInfo;";
            IEnumerable<Employee> employees = _sqlDatabaseProxy.Query<Employee>(sqlString);
            foreach (var item in employees)
            {
                if (item.JobNum.Equals(JobNum) && item.PassWord.Equals(PassWord))
                {
                    employee = item;
                }
            }
            return employee;
        }
        /// <summary>
        /// 插入新的产品型号
        /// </summary>
        /// <param name="production"></param>
        /// <returns></returns>
        public bool InsertProductionModel(Production production)
        {
            try
            {
                string sqlString = "INSERT INTO Production Values(@Model,@ResistanceCount,@QualifiedResistance,@AllowOffset,@Range)";
                _sqlDatabaseProxy.Insert<Production>(sqlString, production);
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 用指定条件查询测试结果
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="stopTime"></param>
        /// <param name="Model"></param>
        /// <returns></returns>
        public IEnumerable<Production> SearchConditianalProduction(DateTime startTime, DateTime stopTime, string Model)
        {
            string tableName = Model + "_MeasureRecord";
            string sqlString = "SELECT * FROM " + tableName + " WHERE MesurTime between #" + startTime.ToString() + "# and #" + stopTime.ToString() + "#;";
            return _sqlDatabaseProxy.Query<Production>(sqlString);
        }
        /// <summary>
        /// 新增员工信息
        /// </summary>
        /// <param name="employee"></param>
        /// <returns></returns>
        public bool InsertEmployee(Employee employee)
        {
            try
            {
                string sqlString = "INSERT INTO UserInfo VALUES(@JobNum,@UserName,@PassWord,@Role);";
                _sqlDatabaseProxy.Insert<Employee>(sqlString, employee);
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 返回所有产品类型
        /// </summary>
        /// <returns></returns>
        public IEnumerable<Production> GetAllProduction()
        {
            string sqlString = "SELECT * FROM Production;";
            return _sqlDatabaseProxy.Query<Production>(sqlString);
        }
        /// <summary>
        /// 搜某个型号的产品
        /// </summary>
        /// <param name="Model"></param>
        /// <returns></returns>
        public Production GetMatchProduction(string Model)
        {
            string sqlString = "SELECT * FROM Production WHERE Model = @model;";
            return _sqlDatabaseProxy.QueryOne<Production>(sqlString, new { model = Model});
        }
        /// <summary>
        /// 更新员工信息
        /// </summary>
        /// <param name="JobNum"></param>
        /// <param name="employee"></param>
        /// <returns></returns>
        public bool UpdateOneEmployee(string JobNum,Employee employee)
        {
            try
            {
                string sqlString = "UPDATE UserInfo SET JobNum = @jobNum,UserName = @userName,PassWord = @passWord,Role = @role WHERE JobNum ='"
                + JobNum + "';";
                _sqlDatabaseProxy.Update<Employee>(sqlString, employee);
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 删除表
        /// </summary>
        /// <param name="production"></param>
        public void DropTable(Production production)
        {
            string tableName = production.Model + "_MeasureRecord";
            string sqlString = "DROP TABLE " + tableName;
            _sqlDatabaseProxy.Execute(sqlString);
        }
        /// <summary>
        /// 查找某一天的测试记录总数
        /// </summary>
        /// <param name="dateTime"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public int QueryCount(DateTime dateTime,string model)
        {
            string tableName = model + "_MeasureRecord";
            string date = dateTime.ToString("yyyy-MM-dd");
            string strStartTime = date + " 00:00";
            string strStopTime = date + " 23:59";
            string sqlString = "SELECT COUNT(*) FROM " + tableName + " WHERE MeasureTime BETWEEN '" + strStartTime + "' AND '"
                + strStopTime + "';";
            int count = _sqlDatabaseProxy.QueryOne<int>(sqlString);
            return count;
        }
        /// <summary>
        /// 查找某一天的合格测试记录总数
        /// </summary>
        /// <param name="dateTime"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public int QueryQualifiedCount(DateTime dateTime, string model)
        {
            string tableName = model + "_MeasureRecord";
            string date = dateTime.ToString("yyyy-MM-dd");
            string strStartTime = date + " 00:00";
            string strStopTime = date + " 23:59";
            string sqlString = "SELECT COUNT(*) FROM " + tableName + " WHERE MeasureTime BETWEEN '" + strStartTime + "' AND '"
                + strStopTime + "' AND Quality = 'Qualified';";
            int count = _sqlDatabaseProxy.QueryOne<int>(sqlString);
            return count;
        }
        /// <summary>
        /// 修改表名
        /// </summary>
        /// <param name="oldModel"></param>
        /// <param name="newModel"></param>
        public void AlterTableName(string oldModel,string newModel)
        {
            string oldTableName = oldModel + "_MeasureRecord";
            string newTableName = newModel + "_MeasureRecord";
            string sqlString = "EXEC sp_rename '" + oldTableName + "','" + newTableName + "';";
            _sqlDatabaseProxy.Execute(sqlString);
        }
        /// <summary>
        ///修改测试表字段
        /// </summary>
        /// <param name="oldCount"></param>
        /// <param name="newCount"></param>
        /// <param name="Model"></param>
        public void AlterColumns(int oldCount,int newCount,string Model)
        {
            string tableName = Model + "_MeasureRecord";
            if (oldCount > newCount)
            {
                int a = oldCount - newCount;
                string dropingColumnsName = "";
                for(int i = 0;i < a;i++)
                {
                    dropingColumnsName += "电阻" + (oldCount - i).ToString() + ",";
                }
                dropingColumnsName = dropingColumnsName.TrimEnd(',');
                string sqlString = "ALTER TABLE " + tableName + " DROP COLUMN " + dropingColumnsName + ";";
                _sqlDatabaseProxy.Execute(sqlString);
            }
            if(oldCount < newCount)
            {
                int a = newCount - oldCount;
                string addingColumnName = "";
                for(int i = 1;i <= a;i++)
                {
                    addingColumnName += "电阻" + (oldCount + i).ToString() + " float,";
                }
                addingColumnName = addingColumnName.TrimEnd(',');
                string sqlString = "ALTER TABLE " + tableName + " ADD " + addingColumnName + ";";
                _sqlDatabaseProxy.Execute(sqlString);
            }
        }
        /// <summary>
        /// 更新产品信息
        /// </summary>
        /// <param name="newProduction"></param>
        /// <param name="oldModel"></param>
        /// <returns></returns>
        public bool UpdateProduction(Production newProduction,string oldModel)
        {
            string sqlString = "Update Production SET Model = @Model,ResistanceCount = @ResistanceCount,"
                + "QualifiedResistance = @QualifiedResistance,AllowOffset = @AllowOffset,Range = @Range WHERE "
                + "Model = '" + oldModel + "';";
            try
            {
                _sqlDatabaseProxy.Update(sqlString, newProduction);
                return true;
            }
            catch
            {
                return false;
            }
        }
    }
}
